Skip Headers

Oracle® XML DB Developer's Guide 10g Release 2 (10.2) Part Number B14259-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 7 XML Schema Storage and Query: Advanced

This chapter describes advanced techniques for storing structured XML schema-based `XMLType` objects.

See Also: Chapter 5, "XML Schema Storage and Query: Basic" for basic information on using XML Schema with Oracle XML DB Chapter 6, "XPath Rewrite" for information on the optimization of XPath expressions in Oracle XML DB Chapter 8, "XML Schema Evolution" for information on updating an XML schema after you have registered it with Oracle XML DB Appendix A, "XML Schema Primer" for an introduction to XML Schema

This chapter contains these topics:

- Generating XML Schemas with DBMS_XMLSCHEMA.GENERATESCHEMA

- Adding Unique Constraints to the Parent Element of an Attribute

- Setting Attribute SQLInline to false for Out-of-Line Storage

- Storing Collections in Out-Of-Line Tables

- Fully Qualified XML Schema URLs

- complexType Extensions and Restrictions in Oracle XML DB

- Oracle XPath Extension Functions to Examine Type Information

- XML Schema: Working With Circular and Cyclical Dependencies

- Cyclical References Between XML Schemas

- Guidelines for Using XML Schema with Oracle XML DB

- Constraints on Repetitive Elements in Schema-Based XML

- Loading and Retrieving Large Documents with Collections

## Generating XML Schemas with DBMS_XMLSCHEMA.GENERATESCHEMA

An XML schema can be generated from an object-relational type automatically using a default mapping. PL/SQL functions `generateSchema` and `generateSchemas` in package `DBMS_XMLSCHEMA` take in a string that has the object type name and another that has the Oracle XML DB XML schema.

- Function `generateSchema` returns an `XMLType` containing an XML schema. It can optionally generate XML schema for all types referenced by the given object type or restricted only to the top-level types.

- Function `generateSchemas` is similar, except that it returns an `XMLSequenceType` value. This is a varray of `XMLType` instances, each of which is an XML schema that corresponds to a different namespace. It also takes an additional optional argument, specifying the root URL of the preferred XML schema location:

  `http://xmlns.oracle.com/xdb/schemas/<schema>.xsd`

  They can also optionally generate annotated XML schemas that can be used to register the XML schema with Oracle XML DB.

See Also: "Creating XMLType Tables and Columns Based on XML Schema"

Example 7-1 Generating an XML Schema with Function GENERATESCHEMA

For example, given the object type:

    CREATE TYPE employee_t AS OBJECT(empno NUMBER(10),
                                     ename VARCHAR2(200),
                                     salary NUMBER(10,2)):

You can generate the schema for this type as follows:

    SELECT DBMS_XMLSCHEMA.generateschema('T1', 'EMPLOYEE_T') FROM DUAL;

This returns a schema corresponding to the type `employee_t`. The schema declares an element named `EMPLOYEE_T` and a `complexType` called `EMPLOYEE_TType`. The schema includes other annotations from `http://xmlns.oracle.com/xdb`.

    DBMS_XMLSCHEMA.GENERATESCHEMA('T1', 'EMPLOYEE_T')
    ------------------------------------------------------------------------
    <xsd:schema targetNamespace="http://ns.oracle.com/xdb/T1"
                xmlns="http://ns.oracle.com/xdb/T1"
                xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                xmlns:xdb="http://xmlns.oracle.com/xdb"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xsi:schemaLocation="http://xmlns.oracle.com/xdb
                                    http://xmlns.oracle.com/xdb/XDBSchema.xsd">
      <xsd:element name="EMPLOYEE_T" type="EMPLOYEE_TType"
                   xdb:SQLType="EMPLOYEE_T" xdb:SQLSchema="T1"/>
      <xsd:complexType name="EMPLOYEE_TType">
        <xsd:sequence>
          <xsd:element name="EMPNO" type="xsd:double" xdb:SQLName="empno"
                       xdb:SQLType="NUMBER"/>
          <xsd:element name="ENAME" type="xsd:string" xdb:SQLName="ename"
                       xdb:SQLType="VARCHAR2"/>
          <xsd:element name="SALARY" type="xsd:double" xdb:SQLName="salary"
                       xdb:SQLType="NUMBER"/>
        </xsd:sequence>
      </xsd:complexType>
    </xsd:schema>

Note: As always: SQL is case-insensitive, but names in SQL code are implicitly uppercase, unless you enclose them in double-quotes. XML is case-sensitive. You must refer to SQL names in XML code using the correct case: uppercase SQL names must be written as uppercase. For example, if you create a table named `my_table` in SQL without using double-quotes, then you must refer to it in XML as "`MY_TABLE`".

## Adding Unique Constraints to the Parent Element of an Attribute

After creating an `XMLType` table based on an XML schema, how can you add a unique constraint to the parent element of an attribute? You may, for example, want to create a unique key based on an attribute of an element that repeats itself (a collection). To create constraints on elements that can occur more than once, store the varray as a table. This is also known as Ordered Collections in Tables (OCT). You can then create constraints on the OCT. Example 7-2 shows how the attribute `No` of element `<PhoneNumber>` can appear more than once, and how a unique constraint can be added to ensure that the same phone number cannot be repeated within the same instance document.

Note: This constraint applies to each collection, and not across all instances. This is achieved by creating a concatenated index with the collection id column. To apply the constraint across all collections of all instance documents, simply omit the collection id column.

Example 7-2 Adding a Unique Constraint to the Parent Element of an Attribute

    BEGIN DBMS_XMLSCHEMA.registerschema('emp.xsd',
       '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
                   xmlns:xdb="http://xmlns.oracle.com/xdb">
          <xs:element name="Employee" xdb:SQLType="EMP_TYPE">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="EmployeeId" type="xs:positiveInteger"/>
                <xs:element name="PhoneNumber" maxOccurs="10">
                  <xs:complexType>
                    <xs:attribute name="No" type="xs:integer"/>
                  </xs:complexType>
                </xs:element>
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:schema>',
       TRUE,
       TRUE,
       FALSE,
       FALSE);
    END;/

    PL/SQL procedure successfully completed.

    CREATE TABLE emp_tab OF XMLType
      XMLSCHEMA "emp.xsd" ELEMENT "Employee"
      VARRAY xmldata."PhoneNumber" STORE AS TABLE phone_tab;

    Table created.

    ALTER TABLE phone_tab ADD UNIQUE (NESTED_TABLE_ID, "No");

    Table altered.

    CREATE TABLE po_xtab OF XMLType; -- The default is CLOB based storage.

    INSERT INTO emp_tab
      VALUES(XMLType('<Employee>
                        <EmployeeId>1234</EmployeeId>
                        <PhoneNumber No="1234"/>
                        <PhoneNumber No="2345"/>
                      </Employee>').createSchemaBasedXML('emp.xsd'));

    1 row created.

    INSERT INTO emp_tab
      VALUES(XMLType('<Employee>
                        <EmployeeId>3456</EmployeeId>
                        <PhoneNumber No="4444"/>
                        <PhoneNumber No="4444"/>
                      </Employee>').createSchemaBasedXML('emp.xsd'));

This returns the expected result:

    *ERROR at line 1:ORA-00001: unique constraint (SCOTT.SYS_C002136) violated

## Setting Attribute SQLInline to false for Out-of-Line Storage

By default, a child element is mapped to an embedded object attribute. However, there may be scenarios where out-of-line storage offers better performance. In such cases, the `SQLInline` attribute can be set to false, and Oracle XML DB generates an object type with an embedded `REF` attribute. `REF` points to another instance of `XMLType` that corresponds to the XML fragment that gets stored out-of-line. Default `XMLType` tables are also created to store the out-of-line fragments.

Figure 7-1 illustrates the mapping of a `complexType` to SQL for out-of-line storage.

Figure 7-1 Mapping complexType to SQL for Out-of-Line Storage

Description of "Figure 7-1 Mapping complexType to SQL for Out-of-Line Storage"

Example 7-3 complexType Mapping - Setting SQLInline to False for Out-of-Line Storage

In this example, attribute `xdb:SQLInline` of element `Addr` is set to `false`. The resulting object type `obj_t2` has a column of type `XMLType` with an embedded `REF` attribute. The `REF` attribute points to another `XMLType` instance created of object type `obj_t1` in table `addr_tab`. Table `addr_tab` has columns `Street` and `City`. The latter `XMLType` instance is stored out of line.

    DECLARE
      doc VARCHAR2(3000) :=
        '<schema xmlns="http://www.w3.org/2001/XMLSchema"
                 targetNamespace="http://www.oracle.com/emp.xsd"
                 xmlns:emp="http://www.oracle.com/emp.xsd"
                 xmlns:xdb="http://xmlns.oracle.com/xdb">
           <complexType name="EmpType" xdb:SQLType="EMP_T">
             <sequence>
               <element name="Name" type="string"/>
               <element name="Age" type="decimal"/>
               <element name="Addr"
                        xdb:SQLInline="false"
                        xdb:defaultTable="ADDR_TAB">
                 <complexType xdb:SQLType="ADDR_T">
                   <sequence>
                     <element name="Street" type="string"/>
                     <element name="City" type="string"/>
                   </sequence>
                 </complexType>
               </element>
             </sequence>
           </complexType>
           <element name="Employee" type="emp:EmpType"
                    xdb:defaultTable="EMP_TAB"/>
         </schema>';
    BEGIN
      DBMS_XMLSCHEMA.registerSchema('emp.xsd', doc);
    END;
    /

When registering this XML schema, Oracle XML DB generates the following types:

    CREATE TYPE addr_t AS OBJECT (SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
                                  street VARCHAR2(4000),
                                  city VARCHAR2(4000));
    CREATE TYPE emp_t AS OBJECT (SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
                                  name VARCHAR2(4000),
                                  age NUMBER,
                                  addr REF XMLType) NOT FINAL;

Two `XMLType` tables are also created: `emp_tab` and `addr_tab`. Table `emp_tab` holds all of the employees, and contains an object reference that points to the address values stored in table `addr_tab`.

The advantage of this model is that it lets you query the out-of-line table (`addr_tab`) directly, to look up the address information. For example, if you want to obtain the distinct city information for all employees, you can query table `addr_tab` directly.

    INSERT INTO emp_tab
      VALUES
        (XMLType('<x:Employee
                      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                      xmlns:x="http://www.oracle.com/emp.xsd"
                      xsi:schemaLocation="http://www.oracle.com/emp.xsd emp.xsd">
                    <Name>Jason Miller</Name>
                    <Age>22</Age>
                    <Addr>
                      <Street>Julian Street</Street>
                      <City>San Francisco</City>
                    </Addr>
                  </x:Employee>'));
    INSERT INTO emp_tab
      VALUES
        (XMLType('<x:Employee
                      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                      xmlns:x="http://www.oracle.com/emp.xsd"
                      xsi:schemaLocation="http://www.oracle.com/emp.xsd emp.xsd">
                    <Name>Jack Simon</Name>
                    <Age>23</Age>
                    <Addr>
                      <Street>Mable Street</Street>
                      <City>Redwood City</City>
                    </Addr>
                  </x:Employee>'));

    -- Table addr_tab stores the addresses, and can be queried directly

    SELECT DISTINCT extractValue(OBJECT_VALUE, '/Addr/City') AS city FROM addr_tab;

    CITY
    ------------------
    Redwood City
    San Francisco

The disadvantage of this storage model is that in order to obtain the entire `Employee` element you need to look up an additional table for the address.

### XPath Rewrite for Out-Of-Line Tables

XPath expressions that involve elements stored out of line are rewritten. The query involves a join with the out-of-line table. For example, the following `EXPLAIN PLAN` shows how a query involving elements `Employee` and `Addr` is handled.

    EXPLAIN PLAN FOR
      SELECT extractValue(OBJECT_VALUE,
                          '/x:Employee/Name',
                          'xmlns:x="http://www.oracle.com/emp.xsd"')
        FROM emp_tab
        WHERE existsNode(OBJECT_VALUE,
                         '/x:Employee/Addr[City="San Francisco"]',
                         'xmlns:x="http://www.oracle.com/emp.xsd"') = 1;

    SELECT PLAN_TABLE_OUTPUT
      FROM table(DBMS_XPLAN.display('plan_table', NULL, 'serial'))/

    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------
    | Id | Operation                   | Name        |
    ---------------------------------------------------------
    |  0 | SELECT STATEMENT            |
    |* 1 | FILTER                      |
    |  2 | TABLE ACCESS FULL           | EMP_TAB     |
    |* 3 | TABLE ACCESS BY INDEX ROWID | ADDR_TAB    |
    |* 4 | INDEX UNIQUE SCAN           | SYS_C003111 |
    ---------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------------
       1 - filter(EXISTS(SELECT 0
                           FROM "SCOTT"."ADDR_TAB" "SYS_ALIAS_1"
                           WHERE "SYS_ALIAS_1"."SYS_NC_OID$"=:B1
                             AND "SYS_ALIAS_1"."SYS_NC00009$"='San Francisco'))
       3 - filter("SYS_ALIAS_1"."SYS_NC00009$"='San Francisco')
       4 - access("SYS_ALIAS_1"."SYS_NC_OID$"=:B1)

In this example, the XPath expression was rewritten to an `exists` subquery that queries table `addr_tab` and joins it with table `emp_tab` using the object identifier column in table `addr_tab`. The optimizer uses a full table scan to scan of all the rows in the employee table, and uses the unique index on the `SYS_NC_OID$` column in the address table to look up the address.

If there are many entries in the `addr_tab`, then you can make this query more efficient by creating an index on column `City`.

    CREATE INDEX addr_city_idx
      ON addr_tab (extractValue(OBJECT_VALUE, '/Addr/City'));

The `EXPLAIN PLAN` for the previous query now uses the `addr_city_idx` index.

    PLAN_TABLE_OUTPUT
    -------------------------------------------------------
    | Id | Operation                  | Name          |
    -------------------------------------------------------
    |  0 | SELECT STATEMENT           |
    |* 1 | FILTER                     |
    |  2 | TABLE ACCESS FULL          | EMP_TAB       |
    |* 3 | TABLE ACCESS BY INDEX ROWID| ADDR_TAB      |
    |* 4 | INDEX RANGE SCAN           | ADDR_CITY_IDX |
    -------------------------------------------------------
    Predicate Information (identified by operation id):
    -------------------------------------------------------
       1 - filter(EXISTS (SELECT 0
                            FROM "SCOTT"."ADDR_TAB" "SYS_ALIAS_1"
                            WHERE "SYS_ALIAS_1"."SYS_NC_OID$"=:B1
                              AND "SYS_ALIAS_1"."SYS_NC00009$"='San Francisco'))
       3 - access("SYS_ALIAS_1"."SYS_NC_OID$"=:B1)
       4 - filter("SYS_ALIAS_1"."SYS_NC00009$"='San Francisco')

See Also: Chapter 6, "XPath Rewrite"

## Storing Collections in Out-Of-Line Tables

You can also map list items to be stored out of line. In this case, instead of a single `REF` column, the parent element will contain a varray of `REF` values that point to the members of the collection. For example, consider the case where we have a list of addresses for each employee and map that to out-of-line storage.

    DECLARE
      doc VARCHAR2(3000) :=
        '<schema xmlns="http://www.w3.org/2001/XMLSchema"
                 targetNamespace="http://www.oracle.com/emp.xsd"
                 xmlns:emp="http://www.oracle.com/emp.xsd"
                 xmlns:xdb="http://xmlns.oracle.com/xdb">
           <complexType name="EmpType" xdb:SQLType="EMP_T2">
             <sequence>
               <element name="Name" type="string"/>
               <element name="Age" type="decimal"/>
               <element name="Addr" xdb:SQLInline="false"
                        maxOccurs="unbounded" xdb:defaultTable="ADDR_TAB2">
                 <complexType xdb:SQLType="ADDR_T2">
                   <sequence>
                     <element name="Street" type="string"/>
                     <element name="City" type="string"/>
                   </sequence>
                 </complexType>
               </element>
             </sequence>
           </complexType>
           <element name="Employee" type="emp:EmpType"
                    xdb:defaultTable="EMP_TAB2"/>
         </schema>';
    BEGIN
      DBMS_XMLSCHEMA.registerSchema('emprefs.xsd', doc);
    END;
    /

When registering this XML schema, Oracle XML DB generates the following:

    CREATE TYPE addr_t2 AS OBJECT (SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
                                   Street VARCHAR2(4000),
                                   City VARCHAR2(4000));
    CREATE TYPE emp_t2  AS OBJECT (SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
                                   Name VARCHAR2(4000),
                                   Age NUMBER,
                                   Addr XDB.XDB$XMLTYPE_REF_LIST_T) NOT FINAL;

Note: As always: SQL is case-insensitive, but names in SQL code are implicitly uppercase, unless you enclose them in double-quotes. XML is case-sensitive. You must refer to SQL names in XML code using the correct case: uppercase SQL names must be written as uppercase in XML code. For example, if you create a table named `my_table` in SQL without using double-quotes, then you must refer to it in XML as "`MY_TABLE`".

The employee type (`emp_t2`) contains a varray of `REF` values to address instead of a single `REF` attribute as in the previous XML schema. By default, this varray of `REF` values is stored in line in the employee table (`emp_tab2`). This storage is ideal for the cases where the more selective predicates in the query are on the employee table. This is because storing the varray in line effectively forces any query involving the two tables to always be driven from the employee table, as there is no way to efficiently join back from the address table. The following example shows the explain plan for a query that selects the names of all San Francisco-based employees and the streets in which they live, in an unnested form.

    EXPLAIN PLAN FOR
      SELECT extractValue(OBJECT_VALUE,
                          '/x:Employee/Name',
                          'xmlns:x="http://www.oracle.com/emp.xsd"') AS name,
             extractValue(value(ad), '/Addr/Street') AS street
        FROM
          emp_tab2,
          table(XMLSequence(extract(OBJECT_VALUE,
                                    '/x:Employee/Addr',
                                    'xmlns:x="http://www.oracle.com/emp.xsd"'))) ad
        WHERE extractValue(value(ad), '/Addr/City') = 'San Francisco';

    Explained.

    SELECT PLAN_TABLE_OUTPUT
      FROM table(DBMS_XPLAN.display('plan_table', NULL, 'serial'));

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    | Id | Operation                        | Name        |
    -------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT                 |             |
    |  1 | NESTED LOOPS                     |             |
    |  2 | NESTED LOOPS                     |             |
    |  3 | TABLE ACCESS FULL                | EMP_TAB2    |
    |  4 | COLLECTION ITERATOR PICKLER FETCH|             |
    |* 5 | TABLE ACCESS BY INDEX ROWID      | ADDR_TAB2   |
    |* 6 | INDEX UNIQUE SCAN                | SYS_C003016 |
    --------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       5 - filter("SYS_ALIAS_2"."SYS_NC00009$"='San Francisco')
       6 - access(VALUE(KOKBF$)="SYS_ALIAS_2"."SYS_NC_OID$")

If there are several `Addr` elements for each employee, then building an index on the `City` element in table `addr_tab2` will help speed up the previous query.

### Out-of-Line Storage: Using an Intermediate Table to Store the List of References

In cases where the number of employees is large, a full table scan of the `emp_tab2` table can be too expensive. The correct approach in this case is to query the address table on the `City` element, and then join back with the employee table.

This can be achieved by storing the varray of `REF` values as a separate table (out of line) and creating an index on the `REF` values in that table. This lets Oracle XML DB query the address table, obtain an object reference (`REF`) to the relevant row, join it with the intermediate table storing the list of `REF` values, and join that table back with the employee table.

The intermediate table can be created by setting `xdb:storeVarrayAsTable="true"` in the XMLSchema definition. This forces the schema registration to store all varray values as separate tables.

Note: Annotation `storeVarrayAsTable="true"` causes element collections to be persisted as rows in an index-organized table (IOT). Oracle Text does not support IOTs. Do not use this annotation if you will need to use Oracle Text indexes for text-based `ora:contains` searches over a collection of elements. See "ora:contains Searches Over a Collection of Elements". To provide for searching with Oracle Text indexes: Set `genTables="false"` during schema registration. Create the necessary tables manually, without using the clause `ORGANIZATION INDEX OVERFLOW`, so that the tables will be heap-organized instead of index-organized (IOT).

    DECLARE
      doc VARCHAR2(3000) :=
        '<schema xmlns="http://www.w3.org/2001/XMLSchema"
                 targetNamespace="http://www.oracle.com/emp.xsd"
                 xmlns:emp="http://www.oracle.com/emp.xsd"
                 xmlns:xdb="http://xmlns.oracle.com/xdb"
                 xdb:storeVarrayAsTable="true">
           <complexType name="EmpType" xdb:SQLType="EMP_T3">
             <sequence>
               <element name="Name" type="string"/>
               <element name="Age" type="decimal"/>
               <element name="Addr" xdb:SQLInline="false"
                        maxOccurs="unbounded" xdb:defaultTable="ADDR_TAB3">
                 <complexType xdb:SQLType="ADDR_T3">
                   <sequence>
                     <element name="Street" type="string"/>
                     <element name="City" type="string"/>
                   </sequence>
                 </complexType>
               </element>
             </sequence>
           </complexType>
           <element name="Employee" type="emp:EmpType"
                    xdb:defaultTable="EMP_TAB3"/>
         </schema>';
    BEGIN
      DBMS_XMLSCHEMA.registerSchema('empreftab.xsd', doc);
    END;
    /

Note: As always: SQL is case-insensitive, but names in SQL code are implicitly uppercase, unless you enclose them in double-quotes. XML is case-sensitive. You must refer to SQL names in XML code using the correct case: uppercase SQL names must be written as uppercase. For example, if you create a table named `my_table` in SQL without using double-quotes, then you must refer to it in XML as "`MY_TABLE`".

In addition to creating types `addr_t3` and `emp_t3` and tables `emp_tab3` and `addr_tab3`, the schema registration also creates the intermediate table that stores the list of `REF` values.

    SELECT TABLE_NAME
      FROM USER_NESTED_TABLES
      WHERE PARENT_TABLE_NAME = 'EMP_TAB3';

    TABLE_NAME
    ------------------------------
    SYS_NTyjtiinHKYuTgNAgAIOXPOQ==

    RENAME "SYS_NTyjtiinHKYuTgNAgAIOXPOQ==" TO emp_tab3_reflist;

    DESCRIBE emp_tab3_reflist

    Name          Null? Type
    ------------- ----  ---------------------------
    COLUMN_VALUE        REF OF XMLTYPE

We can create an index on the `REF` value in this table. Indexes on `REF` values can be only be created if the `REF` is scoped or has a referential constraint. Creating a scope on a `REF` column implies that the `REF` only stores pointers to objects in a particular table. In this example, the `REF` values in table `emp_tab3_reflist` will only point to objects in table `addr_tab3`, so we can create a scope constraint and an index on the `REF` column, as follows.

    ALTER TABLE emp_tab3_reflist ADD SCOPE FOR (column_value) IS addr_tab3;
    CREATE INDEX reflist_idx ON emp_tab3_reflist (column_value);

    CREATE INDEX city_idx ON addr_tab3 p (extractValue(OBJECT_VALUE, '/Addr/City'));

Now, the `EXPLAIN PLAN` for the earlier query shows the use of the `city_idx` index, followed by a join with tables `emp_tab3_reflist` and `emp_tab3`.

    EXPLAIN PLAN FOR
      SELECT extractValue(OBJECT_VALUE,
                          '/x:Employee/Name',
                          'xmlns:x="http://www.oracle.com/emp.xsd"') AS name,
             extractValue(value(ad), '/Addr/Street') AS street
        FROM emp_tab3,
             table(XMLSequence(extract(OBJECT_VALUE,
                                       '/x:Employee/Addr',
                                       'xmlns:x="http://www.oracle.com/emp.xsd"'))) ad
        WHERE extractValue(value(ad), '/Addr/City') = 'San Francisco';

    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------
    | Id | Operation                   | Name        |
    ----------------------------------------------------------------------
    |  0 | SELECT STATEMENT            |             |
    |  1 | NESTED LOOPS                |             |
    |  2 | NESTED LOOPS                |             |
    |  3 | TABLE ACCESS BY INDEX ROWID | ADDR_TAB3   |
    |* 4 | INDEX RANGE SCAN            | CITY_IDX    |
    |* 5 | INDEX RANGE SCAN            | REFLIST_IDX |
    |  6 | TABLE ACCESS BY INDEX ROWID | EMP_TAB3    |
    |* 7 | INDEX UNIQUE SCAN           | SYS_C003018 |
    ----------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       4 - access("SYS_ALIAS_2"."SYS_NC00009$"='San Francisco')
       5 - access("EMP_TAB3_REFLIST"."COLUMN_VALUE"="SYS_ALIAS_2"."SYS_NC_OID$")
       7 - access("NESTED_TABLE_ID"="SYS_ALIAS_1"."SYS_NC0001100012$")

## Fully Qualified XML Schema URLs

By default, XML schema URL names are always referenced within the scope of the current user. In other words, when database users specify XML schema URLs, they are first resolved as the names of local XML schemas owned by the current user.

- If there are no such XML schemas, then they are resolved as names of global XML schemas.

- If there are no global XML schemas either, then Oracle XML DB raises an error.

To permit explicit reference to XML schemas in these cases, Oracle XML DB supports the notion of fully qualified XML schema URLs. In this form, the name of the database user owning the XML schema is also specified as part of the XML schema URL, except that such XML schema URLs belong to the Oracle XML DB namespace:

    http://xmlns.oracle.com/xdb/schemas/<database-user>/<schemaURL-minus-protocol>

Example 7-4 Using a Fully Qualified XML Schema URL

For example, consider the global XML schema with the following URL:

    http://www.example.com/po.xsd

Assume that database user `SCOTT` has a local XML schema with the same URL:

    http://www.example.com/po.xsd

User `JOE` can reference the local XML schema owned by `SCOTT` as follows:

    http://xmlns.oracle.com/xdb/schemas/SCOTT/www.example.com/po.xsd

Similarly, the fully qualified URL for the global XML schema is:

    http://xmlns.oracle.com/xdb/schemas/PUBLIC/www.example.com/po.xsd

## Mapping XML Fragments to Large Objects (LOBs)

You can specify the `SQLType` for a complex element as a Character Large Object (`CLOB`) value or a Binary Large Object (`BLOB`) value, as shown in Figure 7-2. Here the entire XML fragment is stored in a LOB attribute. This is useful when parts of the XML document are seldom queried but are mostly retrieved and stored as single pieces. By storing XML fragments as LOBs, you can save on parsing, decomposition, and recomposition overheads.

Example 7-5 Oracle XML DB XML Schema: Mapping complexType XML Fragments to LOBs

In the following example, the XML schema specifies that the XML fragment element `Addr` uses the attribute `SQLType="CLOB"`:

    DECLARE
      doc VARCHAR2(3000) :=
        '<schema xmlns="http://www.w3.org/2001/XMLSchema"
                 targetNamespace="http://www.oracle.com/emp.xsd"
                 xmlns:emp="http://www.oracle.com/emp.xsd"
                 xmlns:xdb="http://xmlns.oracle.com/xdb">
           <complexType name="Employee" xdb:SQLType="OBJ_T">
             <sequence>
               <element name="Name" type="string"/>
               <element name="Age" type="decimal"/>
               <element name="Addr" xdb:SQLType="CLOB">
                 <complexType >
                   <sequence>
                     <element name="Street" type="string"/>
                     <element name="City" type="string"/>
                   </sequence>
                 </complexType>
               </element>
             </sequence>
           </complexType>
         </schema>';
    BEGIN
      DBMS_XMLSCHEMA.registerSchema('http://www.oracle.com/PO.xsd', doc);
    END;

On registering this XML schema, Oracle XML DB generates the following types and `XMLType` tables:

    CREATE TYPE obj_t AS OBJECT(SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
                                Name VARCHAR2(4000),
                                Age NUMBER,
                                Addr CLOB);

Figure 7-2 Mapping complexType XML Fragments to Character Large Objects (CLOBs)

Description of "Figure 7-2 Mapping complexType XML Fragments to Character Large Objects (CLOBs) "

## complexType Extensions and Restrictions in Oracle XML DB

In XML Schema, `complexType` values are declared based on `complexContent` and `simpleContent`.

- `simpleContent` is declared as an extension of `simpleType`.

- `complexContent` is declared as one of the following:

  - Base type

  - `complexType` extension

  - `complexType` restriction

This section describes the Oracle XML DB extensions and restrictions to `complexType`.

### complexType Declarations in XML Schema: Handling Inheritance

For `complexType`, Oracle XML DB handles inheritance in the XML schema as follows:

- For complexTypes declared to extend other complexTypes, the SQL type corresponding to the base type is specified as the supertype for the current SQL type. Only the additional attributes and elements declared in the sub-complextype are added as attributes to the sub-object-type.

- For complexTypes declared to restrict other complexTypes, the SQL type for the sub-complex type is set to be the same as the SQL type for its base type. This is because SQL does not support restriction of object types through the inheritance mechanism. Any constraints are imposed by the restriction in XML schema.

Example 7-6 Inheritance in XML Schema: complexContent as an Extension of complexTypes

Consider an XML schema that defines a base `complexType` `Address` and two extensions `USAddress` and `IntlAddress`.

    DECLARE
      doc VARCHAR2(3000) :=
        '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
                    xmlns:xdb="http://xmlns.oracle.com/xdb">
           <xs:complexType name="Address" xdb:SQLType="ADDR_T">
             <xs:sequence>
               <xs:element name="street" type="xs:string"/>
               <xs:element name="city" type="xs:string"/>
             </xs:sequence>
           </xs:complexType>
           <xs:complexType name="USAddress" xdb:SQLType="USADDR_T">
             <xs:complexContent>
               <xs:extension base="Address">
                 <xs:sequence>
                   <xs:element name="zip" type="xs:string"/>
                 </xs:sequence>
               </xs:extension>
             </xs:complexContent>
           </xs:complexType>
           <xs:complexType name="IntlAddress" final="#all" xdb:SQLType="INTLADDR_T">
             <xs:complexContent>
               <xs:extension base="Address">
                 <xs:sequence>
                   <xs:element name="country" type="xs:string"/>
                 </xs:sequence>
               </xs:extension>
             </xs:complexContent>
           </xs:complexType>
         </xs:schema>';
    BEGIN
      DBMS_XMLSCHEMA.registerSchema('http://www.oracle.com/PO.xsd', doc);
    END;

Note: Type `intladdr_t` is created as a final type because the corresponding `complexType` specifies the "`final`" attribute. By default, all `complexTypes` can be extended and restricted by other types, so all SQL object types are created as types that are not final.

    CREATE TYPE addr_t AS OBJECT(SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
                                 "street" VARCHAR2(4000),
                                 "city" VARCHAR2(4000)) NOT FINAL;
    CREATE TYPE usaddr_t UNDER addr_t ("zip" VARCHAR2(4000)) NOT FINAL;
    CREATE TYPE intladdr_t UNDER addr_t ("country" VARCHAR2(4000)) FINAL;

Example 7-7 Inheritance in XML Schema: Restrictions in complexTypes

Consider an XML schema that defines a base `complexType` `Address` and a restricted type `LocalAddress` that prohibits the specification of `country` attribute.

    DECLARE
      doc varchar2(3000) :=
        '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
                    xmlns:xdb="http://xmlns.oracle.com/xdb">
           <xs:complexType name="Address" xdb:SQLType="ADDR_T">
             <xs:sequence>
               <xs:element name="street" type="xs:string"/>
               <xs:element name="city" type="xs:string"/>
               <xs:element name="zip" type="xs:string"/>
               <xs:element name="country" type="xs:string" minOccurs="0"
                           maxOccurs="1"/>
             </xs:sequence>
           </xs:complexType>
           <xs:complexType name="LocalAddress" xdb:SQLType="USADDR_T">
             <xs:complexContent>
               <xs:restriction base="Address">
                 <xs:sequence>
                   <xs:element name="street" type="xs:string"/>
                   <xs:element name="city" type="xs:string"/>
                   <xs:element name="zip" type="xs:string"/>
                   <xs:element name="country" type="xs:string"
                               minOccurs="0" maxOccurs="0"/>
                 </xs:sequence>
               </xs:restriction>
             </xs:complexContent>
           </xs:complexType>
         </xs:schema>';
    BEGIN
      DBMS_XMLSCHEMA.registerSchema('http://www.oracle.com/PO.xsd', doc);
    END;

Because inheritance support in SQL does not support a notion of restriction, the SQL type corresponding to the restricted `complexType` is a empty subtype of the parent object type. For the preceding XML schema, the following SQL types are generated:

    CREATE TYPE addr_t AS OBJECT (SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
                                  "street"   VARCHAR2(4000),
                                  "city"     VARCHAR2(4000),
                                  "zip"      VARCHAR2(4000),
                                  "country"  VARCHAR2(4000)) NOT FINAL;
    CREATE TYPE usaddr_t UNDER addr_t;

### Mapping complexType: simpleContent to Object Types

A `complexType` based on a `simpleContent` declaration is mapped to an object type with attributes corresponding to the XML attributes and an extra `SYS_XDBBODY$` attribute corresponding to the body value. The datatype of the body attribute is based on `simpleType` which defines the body type.

Example 7-8 XML Schema complexType: Mapping complexType to simpleContent

    DECLARE
      doc VARCHAR2(3000) :=
        '<schema xmlns="http://www.w3.org/2001/XMLSchema"
                 targetNamespace="http://www.oracle.com/emp.xsd"
                 xmlns:emp="http://www.oracle.com/emp.xsd"
                 xmlns:xdb="http://xmlns.oracle.com/xdb">
           <complexType name="name" xdb:SQLType="OBJ_T">
             <simpleContent>
               <restriction base="string">
               </restriction>
             </simpleContent>
           </complexType>
         </schema>';
    BEGIN
      DBMS_XMLSCHEMA.registerschema('http://www.oracle.com/emp.xsd', doc);
    END;

On registering this XML schema, Oracle XML DB generates the following types and `XMLType` tables:

    CREATE TYPE obj_t AS OBJECT(SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
                                SYS_XDBBODY$ VARCHAR2(4000));

### Mapping complexType: Any and AnyAttributes

Oracle XML DB maps the element declaration, `any`, and the attribute declaration, `anyAttribute`, to `VARCHAR2` attributes (or optionally to Large Objects (LOBs)) in the created object type. The object attribute stores the text of the XML fragment that matches the `any` declaration.

- The `namespace` attribute can be used to restrict the contents so that they belong to a specified namespace.

- The `processContents` attribute within the `any` element declaration, indicates the level of validation required for the contents matching the `any` declaration.

Example 7-9 Oracle XML DB XML Schema: Mapping complexType to Any/AnyAttributes

This XML schema example declares an any element and maps it to the column `SYS_XDBANY$`, in object type `obj_t`. This element also declares that the attribute, `processContents`, skips validating contents that match the any declaration.

    DECLARE
      doc VARCHAR2(3000) :=
        '<schema xmlns="http://www.w3.org/2001/XMLSchema"
                 targetNamespace="http://www.oracle.com/any.xsd"
                 xmlns:emp="http://www.oracle.com/any.xsd"
                 xmlns:xdb="http://xmlns.oracle.com/xdb">
           <complexType name="Employee" xdb:SQLType="OBJ_T">
             <sequence>
               <element name="Name" type="string"/>
               <element name="Age" type="decimal"/>
               <any namespace="http://www/w3.org/2001/xhtml"
                    processContents="skip"/>
             </sequence>
           </complexType>
         </schema>';
    BEGIN
      DBMS_XMLSCHEMA.registerSchema('http://www.oracle.com/emp.xsd', doc);
    END;

This results in the following statement:

    CREATE TYPE obj_t AS OBJECT(SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
                                Name VARCHAR2(4000),
                                Age NUMBER,
                                SYS_XDBANY$ VARCHAR2(4000));

## Oracle XPath Extension Functions to Examine Type Information

Oracle XML DB supports XML schema-based data, where elements and attributes have XML Schema datatype information associated with them. However, XPath 1.0 is not aware of datatype information. Oracle XML DB extends XPath 1.0 with the following Oracle extension functions to support examining datatype information:

- `instanceof`

- `instanceof-only`

These XPath functions are in namespace `http://xmlns.oracle.com/xdb`, which has the predefined prefix `ora`.

An element is an instance of a specified XML Schema datatype if its type is the same as the specified type or is a subtype of the specified type. A subtype of type `T` in the context of XML Schema is a type that extends or restricts `T`, or extends or restricts another subtype of `T`.

For XPath expressions involving XML schema-based data, you can use Oracle XPath function `ora:instanceof-only` to restrict the result set to nodes of a certain datatype, and `ora:instanceof` to restrict the result set to nodes of a certain datatype or its subtypes. For non-schema-based XML data, elements and attributes do not have datatype information, so these functions return false for non-schema-based data.

### ora:instanceof-only XPath Function

Syntax

    ora:instanceof-only(nodeset-expr, typename [, schema-url])

On XML schema-based data, `ora:instanceof-only` evaluates XPath expression `nodeset-expr` and determines the XML Schema datatype for each of the resultant nodes. Expression `nodeset-expr` is typically a relative XPath expression. If the datatype of any of the nodes exactly matches datatype `typename` (a string), which can be qualified with a namespace prefix, then `instanceof-only` returns true; otherwise, it returns false. It returns false for non-schema-based data.

Optional parameter `schema-url` (a string) indicates the schema location URL for the datatype to be matched. If specified, then the `schema-url` parameter must specify the location of the the XML schema that defines the node datatype. If `schema-url` is not specified, the schema location of the node is not checked.

Example 7-10 Using ora:instanceof-only

The following query selects the `Name` attributes of `AE` children of element `Person` that are of datatype `PersonType` (subtypes of `PersonType` are not matched).

    SELECT extract(OBJECT_VALUE,
                   '/p9:Person[ora:instanceof-only(AE,"p9:PersonType")]/AE/Name',
                   'xmlns:p9="person9.xsd" xmlns:ora="http://xmlns.oracle.com/xdb"')
      FROM po_table;

### ora:instanceof XPath Function

Syntax

    ora:instanceof(nodeset-expr, typename [, schema-url])

Oracle XPath function `ora:instanceof` is similar to `ora:instanceof-only`, but it also returns true if the datatype of any of the matching nodes exactly matches a subtype of datatype `typename`.

Example 7-11 Using ora:instanceof

The following query selects the `Name` attributes of `AE` children of element `Person` that are of datatype `PersonType` or of one of its subtypes.

    SELECT extract(OBJECT_VALUE,
                   '/p9:Person[ora:instanceof(AE,"p9:PersonType")]/AE/Name',
                   'xmlns:p9="person9.xsd" xmlns:ora="http://xmlns.oracle.com/xdb"')
      FROM po_table;

The schema-location parameter is typically used in a heterogeneous XML Schema scenario. Heterogeneous XML schema-based data can be present in a single table. If your scenario involves a schema-based table, consider omitting the schema location parameter.

Consider a non-schema-based table of `XMLType`. Each row in the table is an XML document. Suppose that each row contains data for which XML schema information has been specified. If the data in the table is converted to XML schema-based data through a subsequent operation, then the rows in the table could pertain to different XML schemas. In such a case, you can specify not only the name and the namespace of the datatype to be matched, but also the schema-location URL.

Example 7-12 Using ora:instanceof with Heterogeneous XML Schema-Based Data

In the non-schema-based table `non_sch_p_tab`, the following query matches elements of type `PersonType` that pertain to XML schema `person9.xsd`.

    SELECT extract(
             createSchemaBased(
               OBJECT_VALUE),
               '/p9:Person/AE[ora:instanceof(.,"p9:PersonType", "person9.xsd")]',
               'xmlns:p9="person9.xsd" xmlns:ora="http://xmlns.oracle.com/xdb"')
      FROM non_sch_p_tab;

## XML Schema: Working With Circular and Cyclical Dependencies

The W3C XML Schema Recommendation allows `complexTypes` and global elements to contain recursive references. For example, a `complexType` definition can contain an element based on that same `complexType` , or a global element can contain a reference to itself. In both cases the reference can be direct or indirect. This kind of structure allows for instance documents where the element in question can appear an infinite number of times in a recursive hierarchy.

Example 7-13 An XML Schema With Circular Dependency

    <?xml version="1.0" encoding="UTF-8"?>
    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
               xmlns:xdb="http://xmlns.oracle.com/xdb"
               elementFormDefault="qualified" attributeFormDefault="unqualified">
      <xs:element name="person" type="personType" xdb:defaultTable="PERSON_TABLE"/>
      <xs:complexType name="personType" xdb:SQLType="PERSON_T">
        <xs:sequence>
          <xs:element name="decendant" type="personType" minOccurs="0"
                      maxOccurs="unbounded" xdb:SQLName="DESCENDANT"
                      xdb:defaultTable="DESCENDANT_TABLE"/>
        </xs:sequence>
        <xs:attribute name="personName" use="required" xdb:SQLName="PERSON_NAME">
          <xs:simpleType>
            <xs:restriction base="xs:string">
              <xs:maxLength value="20"/>
            </xs:restriction>
          </xs:simpleType>
        </xs:attribute>
      </xs:complexType>
    </xs:schema>

The XML schema in Example 7-13 includes a circular dependency. The `complexType` `personType` consists of a `personName` attribute and a collection of `descendant` elements. The `descendant` element is defined as being of `personType`.

### For Circular XML Schema Dependencies Set GenTables Parameter to TRUE

Oracle XML DB supports XML schemas that define this kind of structure. It does this by detecting the cycles, breaking them, and storing the recursive elements as rows in a separate `XMLType` table that is created during XML schema registration.

Consequently, it is important to ensure that the `genTables` parameter is always set to `TRUE` when registering an XML schema that defines this kind of structure. The name of the table used to store the recursive elements can be specified by adding an `xdb:defaultTable` annotation to the XML schema.

### Handling Cycling Between complexTypes in XML Schema

SQL object types do not allow cycles. Cycles in the XML schema are broken while generating the object types, by introducing a `REF` attribute at the point at which the cycle would be completed. Thus, part of the data is stored out of line, yet it is still retrieved as part of the parent XML document.

Example 7-14 XML Schema: Cycling Between complexTypes

XML schemas permit cycling between definitions of `complexTypes`. Figure 7-3 shows this example, where the definition of `complexType` `CT1` can reference another `complexType` `CT2`, whereas the definition of `CT2` references the first type `CT1`.

XML schemas permit cycling between definitions of `complexTypes`. This is an example of cycle of length two:

    DECLARE
      doc VARCHAR2(3000) :=
        '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
                    xmlns:xdb="http://xmlns.oracle.com/xdb">
           <xs:complexType name="CT1" xdb:SQLType="CT1">
             <xs:sequence>
               <xs:element name="e1" type="xs:string"/>
               <xs:element name="e2" type="CT2"/>
             </xs:sequence>
           </xs:complexType>
           <xs:complexType name="CT2" xdb:SQLType="CT2">
             <xs:sequence>
               <xs:element name="e1" type="xs:string"/>
               <xs:element name="e2" type="CT1"/>
             </xs:sequence>
           </xs:complexType>
         </xs:schema>';
    BEGIN
      DBMS_XMLSCHEMA.registerSchema('http://www.oracle.com/emp.xsd', doc);
    END;

SQL types do not allow cycles in type definitions. However, they do support weak cycles, that is, cycles involving `REF` (reference) attributes. Cyclic XML schema definitions are mapped to SQL object types in such a way that cycles are avoided by forcing `SQLInline="false"` at the appropriate points. This creates a weak SQL cycle.

For the preceding XML schema, the following SQL types are generated:

    CREATE TYPE ct1 AS OBJECT (SYS_XDBPD$  XDB.XDB$RAW_LIST_T,
                               "e1"        VARCHAR2(4000),
                               "e2"        REF XMLType) NOT FINAL;
    CREATE TYPE ct2 AS OBJECT (SYS_XDBPD$  XDB.XDB$RAW_LIST_T,
                               "e1"        VARCHAR2(4000),
                               "e2"        CT1) NOT FINAL;

Figure 7-3 Cross Referencing Between Different complexTypes in the Same XML Schema

Description of "Figure 7-3 Cross Referencing Between Different complexTypes in the Same XML Schema"

Example 7-15 XML Schema: Cycling Between complexTypes, Self-Reference

Another example of a cyclic `complexType` involves the declaration of the `complexType` having a reference to itself. In this example, type `<SectionT>` references itself:

    DECLARE
      doc VARCHAR2(3000) :=
        '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
                    xmlns:xdb="http://xmlns.oracle.com/xdb">
           <xs:complexType name="SectionT" xdb:SQLType="SECTION_T">
             <xs:sequence>
               <xs:element name="title" type="xs:string"/>
               <xs:choice maxOccurs="unbounded">
                 <xs:element name="body" type="xs:string"
                             xdb:SQLCollType="BODY_COLL"/>
                 <xs:element name="section" type="SectionT"/>
               </xs:choice>
             </xs:sequence>
           </xs:complexType>
         </xs:schema>';
    BEGIN
      DBMS_XMLSCHEMA.registerSchema('http://www.oracle.com/section.xsd', doc);
    END;

The following SQL types are generated.

    CREATE TYPE body_coll AS VARRAY(32767) OF VARCHAR2(4000);
    CREATE TYPE section_t AS OBJECT (SYS_XDBPD$  XDB.XDB$RAW_LIST_T,
                                     "title"     VARCHAR2(4000),
                                     "body"      BODY_COLL,
                                     "section"   XDB.XDB$REF_LIST_T) NOT FINAL;

Note: The `section` attribute is declared as a varray of `REF` references to `XMLType` instances. Because there can be more than one occurrence of embedded sections, the attribute is a varray. It is a varray of `REF` references to `XMLType` values, to avoid forming a cycle of SQL objects.

#### How a complexType Can Reference Itself

Assume that your XML schema, identified by "`http://www.oracle.com/PO.xsd`", has been registered. An `XMLType` table, `purchaseorder`, can then be created to store instances conforming to element `PurchaseOrder` of this XML schema, in an object-relational format:

    CREATE TABLE purchaseorder OF XMLType
       ELEMENT "http://www.oracle.com/PO.xsd#PurchaseOrder";

Figure 7-4 illustrates schematically how a `complexType` can reference itself.

Figure 7-4 complexType Self Referencing Within an XML Schema

Description of "Figure 7-4 complexType Self Referencing Within an XML Schema"

See Also: "Cyclical References Between XML Schemas"

Hidden columns are created that correspond to the object type to which the `PurchaseOrder` element has been mapped. In addition, an `XMLExtra` object column is created, to store top-level instance data such as namespace declarations.

Note: `XMLDATA` is a pseudo-attribute of `XMLType` that enables direct access to the underlying object column. See Chapter 4, "XMLType Operations".

### Cyclical References Between XML Schemas

XML schemas can depend on each other in such a way that they cannot be registered one after the other in the usual manner. Examples of such XML schemas follow:

Example 7-16 Cyclic Dependencies

An XML schema that includes another XML schema cannot be created if the included XML schema does not exist.

    BEGIN DBMS_XMLSCHEMA.registerSchema(
      'xm40.xsd',
      '<schema xmlns="http://www.w3.org/2001/XMLSchema"
               xmlns:my="xm40"
               targetNamespace="xm40">
         <include schemaLocation="xm40a.xsd"/>
         <!-- Define a global complextype here -->
         <complexType name="Company">
           <sequence>
             <element name="Name" type="string"/>
             <element name="Address" type="string"/>
           </sequence>
         </complexType>
         <!-- Define a global element depending on included schema -->
         <element name="Emp" type="my:Employee"/>
       </schema>',
      TRUE,
      TRUE,
      FALSE,
      TRUE);
    END;
    /

It can, however, be created with the `FORCE` option:

    BEGIN DBMS_XMLSCHEMA.registerSchema(
      'xm40.xsd',
      '<schema xmlns="http://www.w3.org/2001/XMLSchema"
               xmlns:my="xm40"
               targetNamespace="xm40">
         <include schemaLocation="xm40a.xsd"/>
         <!-- Define a global complextype here -->
         <complexType name="Company">
           <sequence>
             <element name="Name" type="string"/>
             <element name="Address" type="string"/>
           </sequence>
         </complexType>
         <!-- Define a global element depending on included schema -->
         <element name="Emp" type="my:Employee"/>
       </schema>',
      TRUE,
      TRUE,
      FALSE,
      TRUE,
      TRUE);
    END;
    /

Attempts to use this schema and recompile will fail:

    CREATE TABLE foo OF SYS.XMLType XMLSCHEMA "xm40.xsd" ELEMENT "Emp";

Now, create the second XML schema with the `FORCE` option. This should also make the first XML schema valid:

    BEGIN DBMS_XMLSCHEMA.registerSchema(
      'xm40a.xsd',
      '<schema xmlns="http://www.w3.org/2001/XMLSchema"
               xmlns:my="xm40"
               targetNamespace="xm40">
         <include schemaLocation="xm40.xsd"/>
         <!-- Define a global complextype here -->
         <complexType name="Employee">
           <sequence>
             <element name="Name" type="string"/>
             <element name="Age" type="positiveInteger"/>
             <element name="Phone" type="string"/>
           </sequence>
         </complexType>
         <!-- Define a global element depending on included schema -->
         <element name="Comp" type="my:Company"/>
       </schema>',
      TRUE,
      TRUE,
      FALSE,
      TRUE,
      TRUE);
    END;
    /

The XML schemas can each be used to create a table:

    CREATE TABLE foo  OF SYS.XMLType XMLSCHEMA "xm40.xsd"  ELEMENT "Emp";
    CREATE TABLE foo2 OF SYS.XMLType XMLSCHEMA "xm40a.xsd" ELEMENT "Comp";

To register both of these XML schemas, which depend on each other, you must use the `FORCE` parameter in `DBMS_XMLSCHEMA.registerSchema` as follows:

1. Register `s1.xsd` in `FORCE` mode:

       DBMS_XMLSCHEMA.registerSchema("s1.xsd", "<schema ...", ..., FORCE => TRUE)

   At this point, `s1.xsd` is invalid and cannot be used.

2. Register `s2.xsd` in `FORCE` mode:

       DBMS_XMLSCHEMA.registerSchema("s2.xsd", "<schema ..", ..., FORCE => TRUE)

   The second operation automatically compiles `s1.xsd` and makes both XML schemas valid.

See Figure 7-5. The preceding example is illustrated in the lower half of the figure.

Figure 7-5 Cyclical References Between XML Schemas

Description of "Figure 7-5 Cyclical References Between XML Schemas"

## Guidelines for Using XML Schema with Oracle XML DB

This section describes guidelines for using XML schema with Oracle XML DB:

### Using Bind Variables in XPath Expressions

When you use a bind variable, Oracle Database rewrites the queries for the cases where the bind variable is used in place of a string literal value. You can also use the `CURSOR_SHARING` set to force Oracle Database to always use bind variables for all string expressions.

XPath Rewrite with Bind Variables

When bind variables are used as string literals in XPath, the expression can be rewritten to use the bind variables. The bind variable must used in place of the string literal using the concatenation operator (`||`), and it must be surrounded by single-quotes (`'`) or double-quotes (`"`) inside the XPath string. The following example illustrates the use of the bind variable with XPath rewrite.

Example 7-17 Using Bind Variables in XPath

    BEGIN
      DBMS_XMLSCHEMA.registerschema(
        'bindtest.xsd',
        '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
                    xmlns:xdb="http://xmlns.oracle.com/xdb">
           <xs:element name="Employee" xdb:SQLType="EMP_BIND_TYPE">
             <xs:complexType>
               <xs:sequence>
                 <xs:element name="EmployeeId"  type="xs:positiveInteger"/>
                 <xs:element name="PhoneNumber" type="xs:string"/>
               </xs:sequence>
             </xs:complexType>
           </xs:element>
         </xs:schema>',
        TRUE,
        TRUE,
        FALSE,
        FALSE);
    END;
    /

    -- Create table corresponding to the Employee element
    CREATE TABLE emp_bind_tab OF XMLType
      ELEMENT "bindtest.xsd#Employee";

    -- Create an index to illustrate the use of bind variables
    CREATE INDEX employeeId_idx ON emp_bind_tab
      (extractValue(OBJECT_VALUE, '/Employee/EmployeeId'));

    EXPLAIN PLAN FOR
      SELECT extractValue(OBJECT_VALUE, '/Employee/PhoneNumber')
        FROM emp_bind_tab
        WHERE existsNode(OBJECT_VALUE, '/Employee[EmployeeId="'||:1||'"] ') = 1;

    SELECT PLAN_TABLE_OUTPUT
      FROM table(DBMS_XPLAN.display('plan_table', NULL, 'serial'))/

    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------
    | Id | Operation | Name |
    ------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT |
    | 1 | TABLE ACCESS BY INDEX ROWID| EMP_BIND_TAB |
    |* 2 | INDEX RANGE SCAN | EMPLOYEEID_IDX |
    ------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("SYS_ALIAS_1"."SYS_NC00008$"=TO_NUMBER(:1))

The bind variable `:1` is used as a string literal value enclosed by double-quotes (`"`). This allows the XPath expression `'/Employee[EmployeeId=" ' || :1 || '"]'` to be rewritten, and the optimizer can use the `EmployeeId_idx` index to satisfy the predicate.

Setting CURSOR_SHARING to FORCE

With XPath rewrite, Oracle Database changes the input XPath expression to use the underlying columns. This means that for a given XPath there is a particular set of columns or tables that is referenced underneath. This is a compile-time operation, because the shared cursor must know exactly which tables and columns it references. This cannot change with each row or instantiation of the cursor.

Hence if the XPath expression is itself a bind variable, Oracle Database cannot do any rewrites, because each instantiation of the cursor can have totally different XPaths. This is similar to binding the name of the column or table in a SQL query. For example, `SELECT * FROM table(:1)`.

Note: You can specify bind variables on the right side of the query. For example, this query uses the usual bind variable sharing:
    SELECT * FROM purchaseorder
    WHERE extractValue(
            OBJECT_VALUE,
            '/PurchaseOrder/LineItems/LineItem/ItemNumber')
          = :1;

When `CURSOR_SHARING` is set to `FORCE`, by default each string constant including XPath becomes a bind variable. When Oracle Database then encounters SQL functions `extractValue`, `existsNode`, and so on, it looks at the XPath bind variables to check if they are really constants. If so, it uses them and rewrites the query. Hence there is a large difference depending on where the bind variable is used.

## Constraints on Repetitive Elements in Schema-Based XML

After creating an `XMLType` table based on an XML schema, you may need to add a unique constraint to one of the elements. That element can occur more than once. To create constraints on elements that occur more than once in the XML instance document, you must store the varray as a table. This is considered an Ordered Collection in the Table, or OCT. In an OCT, the elements of the varray are stored in separate tables. You can then create constraints on the OCT.

The following example shows the attribute `No` of `<PhoneNumber>` that can appear more than once, and a unique constraint added to ensure that the same number cannot be repeated in the same XML instance document.

Example 7-18 Creating Constraints on Repetitive Elements in a Schema-Based Table

In this example, the constraint applies to each collection and not across all XML instances. This is achieved by creating a concatenated index with the collection `id` column. To apply the constraint across all collections of all instance documents, simply omit the collection `id` column.

    BEGIN DBMS_XMLSCHEMA.registerschema(
      'emp.xsd',
      '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
                  xmlns:xdb="http://xmlns.oracle.com/xdb">
         <xs:element name="Employee" xdb:SQLType="EMP_TYPE">
           <xs:complexType>
             <xs:sequence>
               <xs:element name="EmployeeId" type="xs:positiveInteger"/>
               <xs:element name="PhoneNumber" maxOccurs="10">
                 <xs:complexType>
                   <xs:attribute name="No" type="xs:integer"/>
                 </xs:complexType>
               </xs:element>
             </xs:sequence>
           </xs:complexType>
         </xs:element>
       </xs:schema>',
      TRUE,
      TRUE,
      FALSE,
      FALSE);
    END;
    /

This returns the following:

    PL/SQL procedure successfully completed.

    CREATE TABLE emp_tab OF XMLType
      XMLSCHEMA "emp.xsd" ELEMENT "Employee"
      VARRAY xmldata."PhoneNumber" STORE AS TABLE phone_tab;

This returns:

    Table created.

    ALTER TABLE phone_tab ADD UNIQUE (NESTED_TABLE_ID, "No");

This returns:

    Table altered.

    INSERT INTO emp_tab
      VALUES (XMLType('<Employee>
                         <EmployeeId>1234</EmployeeId>
                         <PhoneNumber No="1234"/>
                         <PhoneNumber No="2345"/>
                       </Employee>').createschemabasedxml('emp.xsd'));

This returns:

    1 row created.

    INSERT INTO emp_tab
      VALUES(XMLType('<Employee>
                        <EmployeeId>3456</EmployeeId>
                        <PhoneNumber No="4444"/>
                        <PhoneNumber No="4444"/>
                      </Employee>').createschemabasedxml('emp.xsd'));

This returns:

    INSERT INTO emp_tab VALUES(XMLType(
     *
     ERROR at line 1:
     ORA-00001: unique constraint (SCOTT.SYS_C002136) violated

## Loading and Retrieving Large Documents with Collections

Two parameters were added to `xdbconfig` in Oracle Database 10g in order to control the amount of memory used by the loading operation. These tunable parameters provide mechanisms to optimize the loading process provided the following conditions are met:

- The document is loaded using either protocols (FTP, HTTP(S), or DAV) or PL/SQL function `DBMS_XDB.createResource`.

- The document is XML schema-based and contains large collections (elements with `maxoccurs` set to a large number).

- The collections of the document are stored as OCTs. This is achieved by either of the following ways:

  - Setting the table properties appropriately in the element definition

  - Setting `xdb:storeVarrayAsTable="true"` in the schema definition, which turns this storage option on for all collections of the schema

Note: Annotation `storeVarrayAsTable="true"` causes element collections to be persisted as rows in an index-organized table (IOT). Oracle Text does not support IOTs. Do not use this annotation if you will need to use Oracle Text indexes for text-based `ora:contains` searches over a collection of elements. See "ora:contains Searches Over a Collection of Elements". To provide for searching with Oracle Text indexes: Set `genTables="false"` during schema registration. Create the necessary tables manually, without using the clause `ORGANIZATION INDEX OVERFLOW`, so the tables will be heap-organized instead of index-organized (IOT).

These optimizations are most useful when there are no triggers on the base table. For situations where triggers appear, the performance may be suboptimal.

The basic idea behind this optimization is that it allows the collections to be swapped into or out of the memory in bounded sizes. As an illustration of this idea consider the following example conforming to a purchase-order XML schema:

    <PurchaseOrder>
      <LineItem itemID="1">
        ...
      </LineItem>
        .
        .
      <LineItem itemID="10240">
        ...
      </LineItem>
    </PurchaseOrder>

The purchase-order document here contains a collection of 10240 `LineItem` elements. Instead of creating the entire document in memory and then pushing it out to disk (a process that leads to excessive memory usage and in some instances a load failure due to inadequate system memory), we create the documents in finite chunks of memory called loadable units. In the example case, if we assume that each line item needs 1K memory and we want to use loadable units of size 512K, then each loadable unit will contain 512K/1K = 512 line items and there will be approximately 20 such units. Moreover, if we wish that the entire memory representation of the document never exceeds 2M in size, we ensure that at any time no more than 2M/512K = 4 loadable units are maintained in the memory. We use an LRU mechanism to swap out the loadable units.

By controlling the size of the loadable unit and the bound on the size of the document you can tune the memory usage and performance of the load or retrieval. Typically a larger loadable unit size translates into lesser number of disk accesses but takes up more memory. This is controlled by the parameter `xdbcore-loadableunit-size` whose default value is 16K. The user can indicate the amount of memory to be given to the document by setting the `xdbcore-xobmem-bound` parameter which defaults to 1M. The values to these parameters are specified in Kilobytes. So, the default value of `xdbcore-xobmem-bound` is 1024 and that of `xdbcore-loadableunit-size` is 16. These are soft limits that provide some guidance to the system as to how to use the memory optimally.

In the preceding example, when we do the FTP load of the document, the pattern in which the loadable units (LU) are created and flushed to the disk is as follows:

    No LUs
    Create LU1[LineItems(LI):1-512]
    LU1[LI:1-512], Create LU2[LI:513-1024]..
    LU1[LI:1-512],...,Create LU4[LI:1517:2028]    <-   Total memory size = 2M
    Swap Out LU1[LI:1-512], LU2[LI:513-1024],...,LU4[LI:1517-2028], Create LU5[LI:2029-2540]
    Swap Out LU2[LI:513-1024], LU3, LU4, LU5, Create LU6[LI:2541-2052]...
    Swap Out LU16, LU17, LU18, LU10, Create LU20[LI:9729-10240]
    Flush LU17,LU18,LU19,LU20

### Guidelines for Setting xdbcore Parameters

Typically if you have 1 Gigabyte of addressable PGA, give about 1/10th of PGA to the document. So, `xobcore-xobmem-bound` should be set to 1/10 of addressable PGA which equals 100M. During full document retrievals and loads, the `xdbcore-loadableunit-size` should be as close to the `xobcore-xobmem-bound` size as possible, within some error. However, in practice, we set it to half the value of `xdbcore-xobmem-bound`; in this case this is 50 M. Starting with these values, try to load the document. In case you run out of memory, lower the `xdbcore-xobmem-bound` and set the `xdbcore-loadableunot-size` to half of its value, and continue until the documents load. In case the load succeeds, try to see if you can increase the `xdbcore-loadableunit-size` to squeeze out better performance. If `xdbcore-loadableunit-size` equals `xdbcore-xobmem-bound`, then try to increase both parameters for further performance improvements.